﻿CREATE PROCEDURE [dbo].[proc_Finance_Outlay_Getlist_A]
	(
		@CompanyId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),		
		@CateOneId int,
		@CateTwoId int,
		@SubjectId int,
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex INT
	)
AS
BEGIN
	
DECLARE @TempStr VARCHAR(500),@StrSql NVARCHAR(4000)
SET @TempStr=''
IF @CompanyId <>0
BEGIN
	SET @TempStr=@TempStr+' R.CompanyIdCur = '+CONVERT(varchar,@CompanyId)+' and '
END
if @CateOneId<>0
begin
	SET @TempStr=@TempStr+'R.CateOneId='+CONVERT(varchar,@CateOneId)+' and '
end	

if @CateTwoId<>0
begin 
	SET @TempStr=@TempStr+'CateTwoId='+CONVERT(varchar,@CateTwoId)+' and '
end

if @SubjectId<>0
begin 
	SET @TempStr=@TempStr+' R.SubjectId='+CONVERT(varchar,@SubjectId)+' and '
end


IF @Stext <>''
BEGIN
	SET @TempStr=@TempStr+'R.Summary like ''%'+ @Stext +'%'' AND '
END

	
SET @StrSql='
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY R.CreateDate Desc)AS Row,
		R.Id,
		R.CreateDate,
		R.TradeDate,
		R.Summary,
		R.SubjectName,
		R.PaymentTypeName,
		R.BankName,
		R.OutlayPrice,
		R.AgentName,
		R.EName,
		R.Description,
		R.CompanyIdCur,A_txflag,OutlayReason ,
		(Select CompanyName From Company Where Id=R.CompanyIdCur) As CompanyName
	From Finance_Outlay R 
	Where  '+@TempStr+'  a_tx=1 and R.TradeDate between '''+@StartDate+''' and '''+@EndDate+'''
	)
	
	Select * From list,(Select Count(Id) as cid,ISNULL(Sum(OutlayPrice),0) as tsum  FROM list) as ab Where Row between '+CONVERT(varchar,@StartIndex)+' and '+CONVERT(varchar,@EndIndex)+'
	'
	
	EXEC(@StrSql)
--PRINT(@StrSql)
end
	RETURN
